﻿using Aspose.Cells;
using Dapper;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using Quartz.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using XiAnAirport.BUS;
using XiAnAirport.Models;

namespace XiAnAirport.DAL
{
    public class UserRoleDAL
    {
        public bool CheckUserName(string name, int userid)
        {
            int count = 0;

            string strSql1 = "SELECT COUNT(1) FROM user WHERE BINARY username=@username";
            if (userid > 0)
            {
                strSql1 += "AND id!=@id";
            }
            try
            {
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql1, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@username", name);
                    mySqlCommand.Parameters.AddWithValue("@id", userid);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }

                if (count > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                return false;
            }
            return false;
        }

        public UserInfo CheckLogin(string loginname, string password)
        {
            UserInfo userInfo = new UserInfo();
            string strSql = string.Format("SELECT * FROM user WHERE username=@username AND password=@password");
            try
            {
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@username", loginname);
                    mySqlCommand.Parameters.AddWithValue("@password", password);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    List<UserInfo> list = ToList<UserInfo>(dataTable);
                    userInfo = list[0];
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            return userInfo;
        }

        public UserInfo CheckLogin1(string loginname, string password)
        {
            string strSql = string.Format("SELECT * FROM user WHERE username='{0}' AND password='{1}' AND IsDelete=0", loginname, password);
            string strSql2 = string.Format("UPDATE user set loginTime='{0}' WHERE username='{1}'", DateTime.Now.ToString(), loginname);
            UserInfo userinfo = SqlHelper<UserInfo>.SqlSingleData(strSql);

            if (userinfo != null)
            {
                SqlHelper<UserInfo>.SqlExecute(strSql2);
            }
            return userinfo;
        }

        public string InsertUser(UserInfo userinfo)
        {
            string strSql = string.Format(@"INSERT INTO user(username,password,fullname,type,mobile,creationDate,creationPerson,IsDelete,isInspection,email,employeeNo,dingUnionId,dingUserId,disabled,functionOption,operateType,organizationId,organizationCode,userId,roleid)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',0,{7},'{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',{18})"
                , userinfo.username, userinfo.password, userinfo.fullname, userinfo.type, userinfo.mobile, DateTime.Now.ToString(), userinfo.creationPerson, userinfo.isInspection, userinfo.email, userinfo.employeeNo, userinfo.dingUnionId, userinfo.dingUserId, userinfo.disabled, userinfo.functionOption, userinfo.operateType, userinfo.organizationId, userinfo.organizationCode, userinfo.userId, userinfo.roleid);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public bool CheckManage(UserInfo user)
        {
            string str = "SELECT ID FROM role WHERE typename='维保主管'";
            string strSql1 = "SELECT COUNT(1) FROM user a LEFT JOIN role b ON a.roleid=b.ID WHERE b.typename='维保主管' AND a.isDelete=0 AND b.isDelete=0 AND a.username!='" + user.username + "'";
            try
            {
                int roleid = SqlHelper<UserInfo>.SqlScalar(str);

                if (user.roleid == roleid)
                {
                    int count = SqlHelper<UserInfo>.SqlScalar(strSql1);
                    if (count > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }

            }
            catch (Exception e)
            {
                return false;
            }
        }

        public string EditUser(UserInfo userinfo)
        {
            string strSql = string.Format("UPDATE user SET username='{0}',password='{1}',fullname='{2}',type='{3}',mobile='{4}',isInspection={5},email='{6}',employeeNo='{7}',dingUnionId='{8}',dingUserId='{9}',disabled='{10}',functionOption='{11}',operateType='{12}',organizationId='{13}', organizationCode='{14}',userId='{15}',roleid={16} WHERE id={17}"
                , userinfo.username, userinfo.password, userinfo.fullname, userinfo.type, userinfo.mobile, userinfo.isInspection, userinfo.email, userinfo.employeeNo, userinfo.dingUnionId, userinfo.dingUserId, userinfo.disabled, userinfo.functionOption, userinfo.operateType, userinfo.organizationId, userinfo.organizationCode, userinfo.userId, userinfo.roleid, userinfo.id);

            if (SqlHelper<UserInfo>.SqlExecute(strSql))
            {
                return "success";
            }
            else
            {
                return "fail";
            }
        }

        public Role LoadRole(int roleid)
        {
            string strSql = "SELECT * FROM role WHERE id=" + roleid;
            return SqlHelper<Role>.SqlSingleData(strSql);
        }

        public string DelUser(int userid)
        {
            string strSql = string.Format("delete FROM user WHERE id={0}", userid);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);
                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string BatchDelUser(string ids)
        {
            string strSql = string.Format("delete FROM user WHERE id in({0})", ids);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);
                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<UserInfo> QueryUser(UserInfo userinfo, string loginname)
        {
            List<UserInfo> list = new List<UserInfo>();
            try
            {
                string strSql = "SELECT a.*,b.menuRole,b.typename FROM user a LEFT JOIN role b ON a.roleid=b.id";
                string sqlwhere = string.Empty;

                if (!string.IsNullOrEmpty(userinfo.fullname))
                {
                    sqlwhere += " a.fullname LIKE @fullname AND";
                }
                if (!string.IsNullOrEmpty(userinfo.username))
                {
                    sqlwhere += " a.username LIKE @username AND";
                }
                if (!string.IsNullOrEmpty(userinfo.employeeNo))
                {
                    sqlwhere += " a.employeeNo LIKE @employeeNo AND";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                if (userinfo.Recordhead > -1 && userinfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + userinfo.fullname + "%";
                    string likename = "%" + userinfo.username + "%";
                    string likearea = "%" + userinfo.employeeNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@fullname", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@username", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@employeeNo", $"%{likearea}%");
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", userinfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", userinfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<UserInfo>(dataTable);
                }
            }
            catch (Exception ex)
            {
                return null;
            }


            return list;
        }

        public int QueryUserCount(UserInfo userinfo, string loginname)
        {
            string strSql = "SELECT COUNT(1) FROM user a LEFT JOIN role b ON a.roleid=b.id";

            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(userinfo.fullname))
            {
                sqlwhere += " a.fullname LIKE @fullname AND";
            }
            if (!string.IsNullOrEmpty(userinfo.username))
            {
                sqlwhere += " a.username LIKE @username AND";
            }
            if (!string.IsNullOrEmpty(userinfo.employeeNo))
            {
                sqlwhere += " a.employeeNo LIKE @employeeNo AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            int count = 0;
            try
            {
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + userinfo.fullname + "%";
                    string likename = "%" + userinfo.username + "%";
                    string likearea = "%" + userinfo.employeeNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@fullname", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@username", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@employeeNo", $"%{likearea}%");
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception e)
            {

            }
            return count;
        }

        public UserInfo CheckExistSelina()
        {
            string strSql = "SELECT id,fullname,username FROM user WHERE roleid=4 AND isDelete=0";

            return SqlHelper<UserInfo>.SqlSingleData(strSql);
        }

        public UserInfo LoadUser(int userid)
        {
            string strSql = "SELECT * FROM user WHERE id=" + userid;
            return SqlHelper<UserInfo>.SqlSingleData(strSql);
        }

        public string ResetPwd(int userid, string pwd)
        {
            string strSql = string.Format("UPDATE user SET password='{0}' WHERE id={1}", pwd, userid);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);
                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string AddRole(Role roleinfo)
        {
            string strSql = string.Format("INSERT INTO role (typename,menuRole,isDelete)VALUES('{0}','{1}',0)", roleinfo.typename, roleinfo.menuRole);


            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public bool CheckRoleName(string name, int roleid)
        {
            string strSql1 = "SELECT COUNT(1) FROM role WHERE typename='" + name + "'";

            if (roleid > 0)
            {
                strSql1 += "AND id!=" + roleid;
            }
            try
            {
                //查询用户名是否存在
                int count = SqlHelper<Role>.SqlScalar(strSql1);
                if (count > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                throw e;
                return false;
            }
            return false;
        }

        public string EditRole(Role roleinfo)
        {
            string strSql = string.Format("UPDATE role SET typename='{0}',menuRole='{1}',isInspection={3} WHERE id={2}"
                , roleinfo.typename, roleinfo.menuRole, roleinfo.id, roleinfo.isInspection);
            string str = "select * FROM role WHERE id=" + roleinfo.id;

            string strSql2 = string.Format("UPDATE `user` SET isInspection={0} WHERE roleid={1} AND username!='sytem'", roleinfo.isInspection, roleinfo.id);
            try
            {
                Role role = SqlHelper<Role>.SqlSingleData(str);
                if (SqlHelper<Role>.SqlExecute(strSql))
                {
                    if (role.isInspection != roleinfo.isInspection)
                    {
                        int count = SqlHelper<Role>.SqlExecuteRows(strSql2);
                        if (count > 0)
                        {
                            return "success";
                        }
                        else
                        {
                            return "fail";
                        }
                    }
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public bool CheckRoleUser(int roleid)
        {
            string strSql1 = "SELECT COUNT(1) FROM user WHERE isDelete=0 AND roleid=" + roleid;
            try
            {
                int rolecount = SqlHelper<Role>.SqlScalar(strSql1);

                if (rolecount > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                return true;
            }

            return false;
        }

        public string DelRole(int roleid)
        {
            string strSql = string.Format("delete FROM role WHERE id={0}", roleid);

            try
            {
                int count = SqlHelper<Role>.SqlExecuteRows(strSql);
                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string BatchDelRole(string ids)
        {
            string strSql = string.Format("delete FROM role WHERE id in ({0})", ids);

            try
            {
                int count = SqlHelper<Role>.SqlExecuteRows(strSql);
                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<Menu> QueryParentMenu()
        {
            string strSql = "SELECT * FROM menu WHERE parentId=0";

            return SqlHelper<Menu>.SqlList(strSql);
        }

        public List<Role> QueryRole(Role roleinfo)
        {
            string strSql = "SELECT * FROM role";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(roleinfo.typename))
            {
                sqlwhere += " typename LIKE '%" + roleinfo.typename + "%' AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (roleinfo.Recordhead > -1 && roleinfo.Recordtail > 0)
            {
                strSql += " LIMIT " + roleinfo.Recordhead + "," + roleinfo.Recordtail;
            }

            return SqlHelper<Role>.SqlList(strSql);
        }

        public int QueryRoleCount(Role roleinfo)
        {
            string strSql = "SELECT COUNT(1) FROM role";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(roleinfo.typename))
            {
                sqlwhere += " typename LIKE '%" + roleinfo.typename + "%' AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            int count = SqlHelper<Role>.SqlScalar(strSql);

            return count;
        }

        public string AssignPermissions(UserInfo userinfo)
        {
            string strSql = string.Format("UPDATE user SET functionOption='{0}' WHERE id={1}", userinfo.functionOption, userinfo.id);

            try
            {
                if (SqlHelper<UserInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string UpdateInspectionAuthority(Role role)
        {
            string strSql = string.Format("UPDATE role SET isInspection={0} WHERE id={1}", role.isInspection, role.id);
            string strSql2 = string.Format("UPDATE `user` SET isInspection={0} WHERE roleid={1} AND username!='sytem'", role.isInspection, role.id);
            try
            {
                int count = SqlHelper<Role>.SqlExecuteRows(strSql);
                if (count > 0)
                {
                    int count2 = SqlHelper<Role>.SqlExecuteRows(strSql2);
                    if (count2 >= 0)
                    {
                        return "success";
                    }
                    else
                    {
                        return "fail";
                    }
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public UserInfo CheckUser(string name)
        {
            string strSql = "SELECT * FROM user WHERE username=@username";
            UserInfo userInfo = new UserInfo();
            try
            {
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@username", name);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    List<UserInfo> list = ToList<UserInfo>(dataTable);
                    userInfo = list[0];
                }
            }
            catch (Exception e)
            {
                return null;
            }
            return userInfo;
        }

        public int QueryAccount(string id)
        {
            string strSql = "SELECT id FROM user where userId='" + id + "'";

            return SqlHelper<OrganizationInfo>.SqlScalar(strSql);
        }

        public string EditSyncAccount(AuthAccountInfo authAccountInfo, int id)
        {
            string strSql = string.Format("UPDATE user SET fullname='{0}',username='{1}',password='{2}',disabled='{3}',mobile='{4}',userId='{5}' WHERE id={6}"
                , authAccountInfo.fullname, authAccountInfo.username, authAccountInfo.password, authAccountInfo.disabled, authAccountInfo.mobile, authAccountInfo.id, id);

            if (SqlHelper<AuthAccountInfo>.SqlExecute(strSql))
            {
                return "success";
            }
            else
            {
                return "fail";
            }
        }

        public string AddAccount(AuthAccountInfo authAccountInfo)
        {
            string strSql = string.Format(@"INSERT INTO user(fullname,username,password,disabled,mobile,userId)VALUES('{0}','{1}','{2}','{3}','{4}','{5}')"
                , authAccountInfo.fullname, authAccountInfo.username, authAccountInfo.password, authAccountInfo.disabled, authAccountInfo.mobile, authAccountInfo.id);

            try
            {
                int count = SqlHelper<AuthAccountInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string EditAccount(AuthAccountInfo authAccountInfo)
        {
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(authAccountInfo.fullname))
            {
                sqlwhere += "fullname='" + authAccountInfo.fullname + "',";
            }
            if (!string.IsNullOrEmpty(authAccountInfo.username))
            {
                sqlwhere += "username='" + authAccountInfo.username + "'";
            }
            if (!string.IsNullOrEmpty(authAccountInfo.password))
            {
                sqlwhere += "password='" + authAccountInfo.password + "'";
            }
            if (!string.IsNullOrEmpty(authAccountInfo.disabled))
            {
                sqlwhere += "disabled='" + authAccountInfo.disabled + "'";
            }
            if (!string.IsNullOrEmpty(authAccountInfo.mobile))
            {
                sqlwhere += "mobile='" + authAccountInfo.mobile + "'";
            }

            if (sqlwhere.Length > 0)
            {
                string strSql = string.Format("UPDATE user SET {0} WHERE userId='{1}'", sqlwhere.Substring(0, sqlwhere.Length - 1), authAccountInfo.id);
                if (SqlHelper<AuthAccountInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            else
            {
                return "success";
            }

        }

        public string DelAccount(AuthAccountInfo authAccountInfo)
        {
            string strSql = string.Format("delete FROM user WHERE userId='{0}'", authAccountInfo.id);

            try
            {
                int count = SqlHelper<AuthAccountInfo>.SqlExecuteRows(strSql);
                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public bool UpdateLoginFail(string username, int num)
        {
            string strSql1 = "UPDATE user SET loginFail=" + num + ",lockTime='" + DateTime.Now.AddMinutes(5).ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE username='" + username + "'";

            try
            {
                int count = SqlHelper<UserInfo>.SqlScalar(strSql1);
                if (count > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return false;
        }

        public bool UnlockLoginFail(int id)
        {
            string strSql1 = "UPDATE user SET loginFail=0,lockTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE id=" + id;

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql1);
                if (count > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return false;
        }

        public UserInfo QuerySyncUser(AuthUserInfo authUserInfo)
        {
            string strSql = "SELECT * FROM user WHERE username='" + authUserInfo.data.loginName + "'";
            return SqlHelper<UserInfo>.SqlSingleData(strSql);
        }
        public List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            List<T> list = new List<T>();
            //确认参数有效,若无效则返回Null
            if (dt == null)
                return list;
            else if (dt.Rows.Count == 0)
                return list;
            Dictionary<string, string> dicField = new Dictionary<string, string>();
            Dictionary<string, string> dicProperty = new Dictionary<string, string>();
            Type type = typeof(T);

            //创建字段字典，方便查找字段名
            foreach (var item in type.GetFields())
            {
                dicField.Add(item.Name.ToLower(), item.Name);
            }
            //创建属性字典，方便查找属性名
            foreach (var item in type.GetProperties())
            {
                dicProperty.Add(item.Name.ToLower(), item.Name);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string memberKey = dt.Columns[j].ColumnName.ToLower();

                    //字段赋值
                    if (dicField.ContainsKey(memberKey))
                    {
                        FieldInfo theField = type.GetField(dicField[memberKey]);
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theField.FieldType;
                            //dbValue = dbValue.ChangeType(memberType);
                            dbValue = Convert.ChangeType(dbValue, memberType);
                        }
                        theField.SetValue(_t, dbValue);
                    }
                    //属性赋值
                    if (dicProperty.ContainsKey(memberKey))
                    {
                        PropertyInfo theProperty = type.GetProperty(dicProperty[memberKey]);
                        if (!theProperty.CanWrite) continue;
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theProperty.PropertyType;
                            if (memberType.IsGenericType && memberType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                NullableConverter newNullableConverter = new NullableConverter(memberType);
                                dbValue = newNullableConverter.ConvertFrom(dbValue);
                            }
                            else
                            {
                                dbValue = Convert.ChangeType(dbValue, memberType);
                            }
                        }
                        theProperty.SetValue(_t, dbValue, null);
                    }
                }
                list.Add(_t);
            }
            return list;
        }
    }
}
